﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'RegistroOTResumenRep')
	BEGIN
		DROP  Procedure  RegistroOTResumenRep
	END
GO 

CREATE PROCEDURE [dbo].[RegistroOTResumenRep]
	(	
		@EmpresaId	AS INT,
		@NumeroOT	AS VARCHAR(30)
	)	
AS
BEGIN	
	SET NOCOUNT ON

	DECLARE @id AS INT

	DECLARE @OTDetalle Table
		(NumeroOT		VARCHAR(30),
		 Total			DECIMAL(10,2))

	DECLARE @OTGastosIndirectos Table
		(NumeroOT		VARCHAR(30),
		 Monto			DECIMAL(10,2))

	DECLARE @OTRubro Table
		(NumeroOT		VARCHAR(30),
		 RubroNombre	VARCHAR(50),
		 Monto			DECIMAL(10,2))


	-- Lista de tablas a ser devueltas a la aplicacion para cargar la hoja de registro	
	SELECT 0 AS TableId, 'Catalog'			AS TableName	UNION
	SELECT 1 AS TableId, 'OrdenTrabajo'		AS TableName	UNION
	SELECT 2 AS TableId, 'OTRubro'			AS TableName
		

	SELECT @id = id 
	FROM Lit_OrdenTrabajo 
	WHERE EmpresaId = @empresaId
	  AND NumeroOT = @numeroOT

	-- OrdenTrabajo
	SELECT o.NumeroOT, e.Nombre AS EmpresaNombre, FechaInicial, FechaFinal, 
		   CASE o.Estado
                WHEN 'A' THEN 'EN PROCESO'
                WHEN 'T' THEN 'FINALIZADA'
			END AS Estado, o.UnidadesSolicitadas As Cantidad, o.UnidadesProducidas AS CantidadProducida
	FROM lit_ordenTrabajo o
	INNER JOIN ld_general..empresas e ON o.empresaId = e.id
	WHERE o.Id = @id

	-- OT Detalle (Mano de obra)
	INSERT INTO @OTDetalle
		SELECT ot.NumeroOT, 
			SUM( (ISNULL(HorasRegular,0) * ISNULL(CostoOperarioxHora,0))
				+(ISNULL(HorasExtra,0) * ISNULL(CostoOperarioxHora,0)*1.5) 
				+(ISNULL(HorasDoble,0) * ISNULL(CostoOperarioxHora,0)*2.0)) AS Total
		FROM Lit_OTDetalle o
		INNER JOIN Lit_OrdenTrabajo ot ON ot.Id = o.OTId
		INNER JOIN Lit_Operario op ON o.operarioId = op.id
		WHERE OTId = @id	  
		GROUP BY ot.NumeroOT
		UNION
		SELECT ot.NumeroOT, 
			SUM( (ISNULL(HorasRegular,0) * ISNULL(CostoAyudantexHora,0))
				+(ISNULL(HorasExtra,0) * ISNULL(CostoAyudantexHora,0)*1.5) 
				+(ISNULL(HorasDoble,0) * ISNULL(CostoAyudantexHora,0)*2.0)) AS Total
		FROM Lit_OTDetalle o
		INNER JOIN Lit_OrdenTrabajo ot ON ot.Id = o.OTId
		INNER JOIN Lit_Operario op ON o.ayudanteId = op.id
		WHERE OTId = @id	  
		GROUP BY ot.NumeroOT


	INSERT INTO @OTRubro
		SELECT NumeroOT, 'Mano de Obra Utilizada' As RubroNombre, SUM(Total) AS Monto
		FROM @OTDetalle
		GROUP BY NumeroOT
	
	-- OT Material
	INSERT INTO @OTRubro
		SELECT ot.NumeroOT, 'Materiales Utilizados' As RubroNombre, SUM(CostoArticuloTotal) AS Monto
		FROM Lit_OTMaterial m
		INNER JOIN Lit_OrdenTrabajo ot ON ot.Id = m.OTId
		INNER JOIN Articulo a ON m.ArticuloId = a.Art_id
		WHERE OTId = @id
		AND a.Emp_Id = @empresaId
		GROUP BY ot.NumeroOT

	-- OT Gastos Directos
	INSERT INTO @OTRubro
		SELECT ot.NumeroOT, 'Gastos Directos Aplicados' As RubroNombre, SUM(otg.Monto) As Monto
		FROM Lit_OTGasto otg
		INNER JOIN Lit_Gasto g ON g.Id = otg.GastoId
		INNER JOIN Lit_OrdenTrabajo ot ON ot.Id = otg.OTId
		WHERE OTId = @id AND g.Tipo = 'D'
		GROUP BY ot.NumeroOT

	-- OT Gastos Indirectos
	INSERT INTO @OTGastosIndirectos
		SELECT ot.NumeroOT, otg.Monto
		FROM Lit_OTGasto otg
		INNER JOIN Lit_Gasto g ON g.Id = otg.GastoId
		INNER JOIN Lit_OrdenTrabajo ot ON ot.Id = otg.OTId
		WHERE OTId = @id AND g.Tipo = 'I'
		UNION
		SELECT NumeroOT, ISNULL(ManoObraIndirecta,0) As Monto
		FROM Lit_OrdenTrabajo
		WHERE Id = @id
		AND ManoObraIndirecta IS NOT NULL
		AND ManoObraIndirecta > 0

	INSERT INTO @OTRubro
		SELECT NumeroOT, 'Gastos Indirectos Aplicados' As RubroNombre, SUM(Monto) As Monto
		FROM @OTGastosIndirectos
		GROUP BY NumeroOT

	INSERT INTO @OTRubro
		SELECT ot.NumeroOT, 'Máquinas Utilizadas' As RubroNombre, SUM(CostoMaquina) AS Monto
		FROM Lit_OTDetalle o
		INNER JOIN Lit_OrdenTrabajo ot ON ot.Id = o.OTId
		WHERE OTId = @id
		GROUP BY ot.NumeroOT

	SELECT *
	FROM @OTRubro
           	            
	SET NOCOUNT OFF
END
GO


